package com.yeren.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.yeren.core.dao.userDao;
import com.yeren.core.entity.PageBean;
import com.yeren.core.entity.User;
import com.yeren.utils.DBUtil;
import com.yeren.utils.StringUtil;



/**
 * liubiao
 */
public class userDaoImpl implements userDao{
	Connection conn=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	private static Logger logger = Logger.getLogger(userDaoImpl.class);
	public int save(User user) {
		String sql="insert into user values(null,?,?,?,?,?,?,?,?,?,?)";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, user.getAccount());
				pstmt.setString(2, user.getPassword());
				pstmt.setString(3, user.getName());
				pstmt.setString(4, user.getSex());
				pstmt.setInt(5, user.getAge()!=null?user.getAge():0);
				pstmt.setDate(6, user.getBirthday());
				pstmt.setString(7, user.getPicture());
				pstmt.setString(8, user.getProvince());
				pstmt.setString(9, user.getCity());
				pstmt.setString(10, user.getDescription());
				int save = pstmt.executeUpdate();
				if(save==1){
					logger.info("保存成功!");
				}
				return save;
				
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(null, pstmt, conn);
			}
		}
		return 0;
	}

	public int delete(int userId) {
		String sql="delete from user where userId=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setInt(1, userId);
				int delete = pstmt.executeUpdate();
				if(delete==1){
					logger.info("删除成功");
				}
				return delete;
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(null, pstmt, conn);
			}
			
		}
		return 0;
	}

	public int update(User user) {
		String sql="update user set account=?,name=?,sex=?,age=?,birthday=?,picture=?,province=?,city=?,description=? where userId=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, user.getAccount());
				pstmt.setString(2, user.getName());
				pstmt.setString(3, user.getSex());
				pstmt.setInt(4, user.getAge()!=null?user.getAge():0);
				pstmt.setDate(5, user.getBirthday());
				pstmt.setString(6, user.getPicture());
				pstmt.setString(7, user.getProvince());
				pstmt.setString(8, user.getCity());
				pstmt.setString(9, user.getDescription());
				pstmt.setInt(10, user.getUserId());
				int update = pstmt.executeUpdate();
				if(update==1){
					logger.info("修改成功");
				}
				return update;
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{}
			DBUtil.close(null, pstmt, conn);
		}
		return 0;
	}

	public User findById(int userId) {
		String sql="select * from user where userId=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setInt(1, userId);
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					User u=new User();
					u.setUserId(rs.getInt(1));
					u.setAccount(rs.getString(2));
					u.setPassword(rs.getString(3));
					u.setName(rs.getString(4));
					u.setSex(rs.getString(5));
					u.setAge(rs.getInt(6));
					u.setBirthday(rs.getDate(7));
					u.setPicture(rs.getString(8));
					u.setProvince(rs.getString(9));
					u.setCity(rs.getString(10));
					u.setDescription(rs.getString(11));
					return u;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return null;
	}

	public List<User> findAll(User user,PageBean pb) {
		StringBuffer sb=new StringBuffer("select * from user u where 1=1");
		if(StringUtil.isNotEmpty(user.getName())){
			sb.append(" and u.userName like '%"+user.getName()+"%'");
		}
		if(user.getAccount()!=null){
			sb.append(" and u.account like '%"+user.getAccount()+"%'");
		}
		if(pb!=null){
			sb.append(" limit "+pb.getStart()+","+pb.getRows());
		}
		conn=DBUtil.getConn();
		List<User> list=new ArrayList<User>();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sb.toString());
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					User u=new User();
					u.setUserId(rs.getInt(1));
					u.setAccount(rs.getString(2));
					u.setPassword(rs.getString(3));
					u.setName(rs.getString(4));
					u.setSex(rs.getString(5));
					u.setAge(rs.getInt(6));
					u.setBirthday(rs.getDate(7));
					u.setPicture(rs.getString(8));
					u.setProvince(rs.getString(9));
					u.setCity(rs.getString(10));
					u.setDescription(rs.getString(11));
					list.add(u);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return list;
	}

	public boolean isExitAccount(String account) {
		String sql="select * from user where account=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,account);
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					logger.info("账号已存在");
					return true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return false;
	}

	public int modifyPassword(User user) {
		String sql="update user set password=? where userId=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,user.getPassword());
				pstmt.setInt(2, user.getUserId());
				int modifyPassword = pstmt.executeUpdate();
				if(modifyPassword==1){
					logger.info("修改密码成功");
				}
				return modifyPassword;
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return 0;
	}

	public User login(User user) {
		String sql="select * from user where account=? and password=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, user.getAccount());
				pstmt.setString(2,user.getPassword());
				rs=pstmt.executeQuery();
				while(rs.next()){
					logger.info("登录成功");
					User u=new User();
					u.setUserId(rs.getInt(1));
					u.setAccount(rs.getString(2));
					u.setPassword(rs.getString(3));
					u.setName(rs.getString(4));
					u.setSex(rs.getString(5));
					u.setAge(rs.getInt(6));
					u.setBirthday(rs.getDate(7));
					u.setPicture(rs.getString(8));
					u.setProvince(rs.getString(9));
					u.setCity(rs.getString(10));
					u.setDescription(rs.getString(11));
					return u;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return null;
	}

	public int getSum() {
		String sql="select count(*) from user";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				
				while(rs.next()){
					int num = rs.getInt(1);
					return num;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return 0;
	}
}
